package com.zkyc.framework.common.mapper;

import com.alibaba.fastjson.JSONObject;
import com.zkyc.framework.common.pojo.Approve;
import com.zkyc.framework.common.vo.website.ApproveContentVO;
import com.zkyc.framework.common.vo.website.ApproveProcessContentVO;
import org.apache.ibatis.annotations.Mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * (Approve)表数据库访问层
 *
 * @author libingchuan
 * @since 2023-02-15 15:56:38
 */
@Mapper
public interface ApproveMapper extends BaseMapper<Approve> {

    @Select({"<script>" +
            "SELECT " +
            " c.*, " +
            " d.center_name, " +
            " e.column_name  " +
            "FROM " +
            " ( " +
            " SELECT " +
            "  a.id, " +
            "  a.content_title, " +
            "  a.center_id, " +
            "  a.column_id, " +
            "  b.create_date,  " +
            "  b.created_by  " +
            " FROM " +
            "  web_content a " +
            "  RIGHT JOIN web_approve b ON a.id = b.pid  " +
            " where b.type = 1 " +
            "  and a.is_deleted = 0  " +
            "  and b.is_deleted = 0  " +
            " <if test='status!=null and status !=\"\" '>" +
            " and b.status = #{status} " +
            " </if>" +
            " <if test='level!=null and level !=\"\" '>" +
            " and b.level = #{level} " +
            " </if>" +
/*            " <if test='showStatus == 3 '>" +
            " and a.status = 0 " +
            " </if>" +*/
/*            " <if test='showStatus == 4 '>" +
            " and (a.status = 1 or a.status = 2) " +
            " </if>" +*/
            " <if test='centerId!=null and centerId !=\"\" '>" +
            " and b.center_id = #{centerId} " +
            " </if>" +
            " <if test='id!=null and id !=\"\" '>" +
            " and a.id = #{id} " +
            " </if>" +
            " <if test=' createdBy!=null and  createdBy !=\"\" '>" +
            " and a. created_by like  CONCAT('%',#{createdBy},'%')  " +
            " </if>" +
            " <if test='contentTitle!=null and contentTitle !=\"\" '>" +
            " and a.content_title like  CONCAT('%',#{contentTitle},'%') " +
            " </if>" +
            " ) c, " +
/*            " <if test='showStatus == 0 or showStatus == 1 '>" +
            " web_approve f," +
            " web_quote_content g," +
            " </if>" +*/
            " web_center d, " +
            " web_column e  " +
            "WHERE " +
            " c.center_id = d.id  " +
            " AND c.column_id = e.id" +
            " <if test='columnName!=null and columnName !=\"\" '>" +
            " and e.column_name like  CONCAT('%',#{columnName},'%')  " +
            " </if>" +
/*            " <if test='showStatus == 0 or showStatus == 1 '>" +
            " AND f.type = 3" +
            " And f.pid = g.content_id " +
            " AND c.id = g.content_id" +
            " AND f.status = #{showStatus}" +
            " </if>" +*/
            "</script>"
    })
    List<ApproveProcessContentVO> contentList(String level, String centerId, String status, String id, String createdBy,
                                              String contentTitle, String columnName, String showStatus);

    @Select({"<script>" +
            "SELECT " +
            " a.id, " +
            " a.content_title, " +
            " a.content, " +
            " a.gmt_created, " +
            " a.created_by, " +
            " b.remark1, " +
            " b.approve_date, " +
            " b.remark2, " +
            " b.level, " +
            " b.transmit_date  " +
            "FROM " +
            " web_content a " +
            " RIGHT JOIN web_approve b ON a.id = b.pid  " +
            " where a.id = #{id} " +
            " and b.type = 1 " +
            " and b.is_deleted = 0 " +
            "</script>"
    })
    ApproveContentVO contentDetail(String id);

    /*    @Select({"<script>" +
                "SELECT " +
                " c.quote_id, " +
                " d.content_title, " +
                " d.created_by, " +
                " d.content, " +
                " c.remark1 as remark, " +
                " c.approve_date " +
                "FROM " +
                " web_content d, ( " +
                " SELECT " +
                "  a.content_id, " +
                "  a.id as quote_id, " +
                "  b.remark1, " +
                "  b.approve_date  " +
                " FROM " +
                "  web_quote_content a " +
                "  RIGHT JOIN web_approve b ON a.id = b.pid  " +
                " WHERE " +
                "  a.id = b.pid  " +
                " ) c  " +
                "WHERE " +
                " d.id = c.content_id  " +
                " AND c.quote_id = #{id}" +
                "</script>"
        })*/
    @Select({"<script>" +
            "SELECT " +
            " a.id, " +
            " a.content_title, " +
            " a.content, " +
            " a.gmt_created, " +
            " a.created_by, " +
            " b.remark1 as remark3, " +
            " b.approve_date, " +
            " b.remark2, " +
            " b.level, " +
            " b.transmit_date  " +
            "FROM " +
            " web_content a " +
            " RIGHT JOIN web_approve b ON a.id = b.pid  " +
            " where a.id = #{id} " +
            " and b.type = 3 " +
            "</script>"
    })
    ApproveContentVO quoteDetail(String id);


    @Select({"<script>" +
            "SELECT " +
            " a.id, " +
            " a.user_code, " +
            " a.name, " +
            " a.position, " +
            " b.remark1, " +
            " b.approve_date, " +
            " b.remark2, " +
            " b.transmit_date  " +
            "FROM " +
            " web_pi a " +
            " RIGHT JOIN web_approve b ON a.id = b.pid  " +
            " where a.id = #{id} " +
            "</script>"
    })
    JSONObject piDetail(String id);

    @Select({"<script>" +
            "SELECT " +
            " c.*, " +
            " d.center_name as centerName, " +
            " e.column_name as quoteTo, " +
            " f.id, " +
            " f.content_title as contentTitle, " +
            " g.column_name as columnName" +
            " FROM " +
            " ( " +
            " SELECT " +
            "  a.id as quoteId, " +
            "  a.column_id as columnId, " +
            "  a.content_id as contentId, " +
            "  b.center_id as centerId, " +
            "  b.create_date as createDate, " +
            "   b.created_by as createdBy " +
            " FROM " +
            "  web_quote_content a " +
            "  RIGHT JOIN web_approve b ON a.content_id = b.pid  " +
            "  where b.LEVEL = 3  " +
            "  and b.is_deleted = 0  " +
            " <if test='status!=null and status !=\"\" '>" +
            " and b.status = #{status} " +
            " </if>" +
            " <if test='centerId!=null and centerId !=\"\" '>" +
            " and b.center_id = #{centerId} " +
            " </if>" +
            " ) c, " +
            " web_center d, " +
            " web_column e , " +
            " web_content f, " +
            " web_column g " +
            "WHERE " +
            " c.centerid = d.id  " +
            " AND c.columnid = e.id " +
            " AND c.contentid = f.id " +
            " AND f.column_id = g.id" +
            " <if test='columnName!=null and columnName !=\"\" '>" +
            " and g.column_name like  CONCAT('%',#{columnName},'%')  " +
            " </if>" +
            " <if test='id!=null and id !=\"\" '>" +
            " and f.id = #{id} " +
            " </if>" +
            " <if test=' createdBy!=null and  createdBy !=\"\" '>" +
            " and f. created_by like  CONCAT('%',#{createdBy},'%')  " +
            " </if>" +
            " <if test='contentTitle!=null and contentTitle !=\"\" '>" +
            " and f.content_title like  CONCAT('%',#{contentTitle},'%')  " +
            " </if>" +
            "</script>"
    })
    List<JSONObject> quoteList(String centerId, String status, String id, String createdBy, String contentTitle, String columnName);

    @Select({"<script>" +
            "SELECT " +
            " a.id, " +
            " a.organization_one as organization_name, " +
            " a.organization_two as center_name, " +
            " a.user_code, " +
            "a.NAME, " +
            "b.create_date  " +
            "FROM " +
            " web_pi a " +
            " RIGHT JOIN web_approve b ON a.id = b.pid  " +
            "WHERE " +
            " b.type = 2" +
            " and b.is_deleted = 0 " +
            " <if test='level!=null and level !=\"\" '>" +
            " and b.level = #{level} " +
            " </if>" +
            " <if test='status!=null and status !=\"\" '>" +
            " and b.status = #{status} " +
            " </if>" +
            " <if test='centerName!=null and centerName !=\"\" '>" +
            " and a.organization_two = #{centerName} " +
            " </if>" +
            " <if test='organizationName!=null and organizationName !=\"\" '>" +
            " and a.organization_one = #{organizationName} " +
            " </if>" +
            " <if test='centerId!=null and centerId !=\"\" '>" +
            " and b.center_id = #{centerId} " +
            " </if>" +
            "</script>"
    })
    List<JSONObject> piList(String level, String centerId, String centerName, String organizationName, String status);


}

